<?php
/**
 * Copyright (c) 2008 Pascal MARTIN <contact@pascal-martin.fr>,
 *                    Rémi PERROUD <remi.perroud@gmail.com>
 *      http://code.google.com/p/simpleguestbook/
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 *
 * @author Pascal MARTIN
 * @version $Id: create-db.php 36 2008-09-27 10:43:34Z pascal.martin.pmn $
 *
 */


$pdo = new PDO('sqlite:' . dirname(__FILE__) . '/guestbook.sqlite.db');

if ($pdo->query('select * from users limit 0, 1') !== false) {
    $pdo->exec('drop table users') or die (print_r($pdo->errorInfo(), true));
}

if ($pdo->query('select * from comments limit 0, 1') !== false) {
    $pdo->exec('drop table comments') or die (print_r($pdo->errorInfo(), true));
}

$query_create_table = <<<QUERY_CREATE_TABLE_USERS
create table users
(
    id integer  primary key,
    login varchar(32) not null collate nocase,
    password varchar(40) not null,
    email varchar(64) not null
)
QUERY_CREATE_TABLE_USERS;
echo $query_create_table . "\n";
if ($pdo->exec($query_create_table) === false) {
    die (print_r($pdo->errorInfo(), true));
}
$sql = 'create index ix_identification on users (login, password)';
echo $sql . "\n";
if ($pdo->exec($sql) === false) {
    die (print_r($pdo->errorInfo(), true));
}

$query_create_table = <<<QUERY_CREATE_TABLE_COMMENTS
create table comments
(
    id integer primary key,
    id_user integer not null,
    date datetime not null,
    texte text not null,
    is_visible boolean not null default 1
)
QUERY_CREATE_TABLE_COMMENTS;
echo $query_create_table . "\n";
if ($pdo->exec($query_create_table) === false) {
    die (print_r($pdo->errorInfo(), true));
}
$sql = 'create index ix_posteur on comments (id_user)';
echo $sql . "\n";
if ($pdo->exec($sql) === false) {
    die (print_r($pdo->errorInfo(), true));
}

$users = array(
    array(
        'login' => 'user1',
        'password' => sha1('123456'),
        'email' => 'user1@example.com',
    ),
    array(
        'login' => 'user2',
        'password' => sha1('123456'),
        'email' => 'user2@example.com',
    ),
);
foreach ($users as $user) {
    $login_escaped = $pdo->quote($user['login']);
    $password_escaped = $pdo->quote($user['password']);
    $email_escaped = $pdo->quote($user['email']);
    $sql = "insert into users (login, password, email) values ($login_escaped, $password_escaped, $email_escaped)";
    echo $sql . "\n";
    if ($pdo->exec($sql) === false) {
        die (print_r($pdo->errorInfo(), true));
    }
}


$comments = array(
    array(
        'id_user' => 1,
        'date' => date('c', strtotime('2007-12-25 08:35:12')),
        'texte' => "Texte du premier commentaire",
        'is_visible' => true,
    ),
    array(
        'id_user' => 1,
        'date' => date('c', strtotime('2008-02-14 12:45:26')),
        'texte' => "Hello, World!",
        'is_visible' => true,
    ),
    array(
        'id_user' => 2,
        'date' => date('c', strtotime('2008-06-21 13:41:05')),
        'texte' => "Commentaire avec du code <strong>HTML</strong> !",
        'is_visible' => true,
    ),
    array(
        'id_user' => 2,
        'date' => date('c', strtotime('2008-06-22 10:38:45')),
        'texte' => "Commentaire non visible (spam, par exemple) !",
        'is_visible' => false,
    ),
);
foreach ($comments as $comment) {
    $id_user = intval($comment['id_user']);
    $date_escaped = $pdo->quote($comment['date']);
    $texte_escaped = $pdo->quote($comment['texte']);
    $is_visible = intval($comment['is_visible']);
    $sql = "insert into comments (id_user, date, texte, is_visible) values ($id_user, $date_escaped, $texte_escaped, $is_visible)";
    echo $sql . "\n";
    if ($pdo->exec($sql) === false) {
        die (print_r($pdo->errorInfo(), true));
    }
}
